{"cells":[{"cell_type":"markdown","id":"3ddeb442","metadata":{"id":"3ddeb442"},"source":["# 构建一个基于SQL数据的问答系统 | 🦜️🔗 LangChain\n","\n","[https://python.langchain.com/v0.2/docs/tutorials/sql_qa/](https://python.langchain.com/v0.2/docs/tutorials/sql_qa/)\n","\n","## 配置"]},{"cell_type":"code","execution_count":1,"id":"32aadeef","metadata":{"executionInfo":{"elapsed":30076,"status":"ok","timestamp":1719879899374,"user":{"displayName":"李辉","userId":"12972001611808140221"},"user_tz":-480},"id":"32aadeef","colab":{"base_uri":"https://localhost:8080/"},"outputId":"70730247-b9a6-473c-ccb4-33503347735d"},"outputs":[{"output_type":"stream","name":"stdout","text":["\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m975.5/975.5 kB\u001b[0m \u001b[31m6.5 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n","\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m2.2/2.2 MB\u001b[0m \u001b[31m12.6 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n","\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m45.9/45.9 kB\u001b[0m \u001b[31m2.8 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n","\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m85.5/85.5 MB\u001b[0m \u001b[31m7.8 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n","\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m332.8/332.8 kB\u001b[0m \u001b[31m9.7 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n","\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m127.4/127.4 kB\u001b[0m \u001b[31m5.0 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n","\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m327.5/327.5 kB\u001b[0m \u001b[31m19.6 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n","\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m1.1/1.1 MB\u001b[0m \u001b[31m37.7 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n","\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m49.2/49.2 kB\u001b[0m \u001b[31m2.6 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n","\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m145.0/145.0 kB\u001b[0m \u001b[31m2.5 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n","\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m75.6/75.6 kB\u001b[0m \u001b[31m7.1 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n","\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m77.9/77.9 kB\u001b[0m \u001b[31m6.3 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n","\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m58.3/58.3 kB\u001b[0m \u001b[31m4.8 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n","\u001b[?25h"]}],"source":["pip install --upgrade --quiet  langchain langchain-community langchain-openai faiss-gpu"]},{"cell_type":"code","source":["pip install langgraph==0.0.66"],"metadata":{"colab":{"base_uri":"https://localhost:8080/"},"id":"dGrv_I4SNfH4","executionInfo":{"status":"ok","timestamp":1719879918707,"user_tz":-480,"elapsed":14876,"user":{"displayName":"李辉","userId":"12972001611808140221"}},"outputId":"4be5c13f-c930-499b-a834-cd9e6205f79b"},"id":"dGrv_I4SNfH4","execution_count":2,"outputs":[{"output_type":"stream","name":"stdout","text":["Collecting langgraph==0.0.66\n","  Downloading langgraph-0.0.66-py3-none-any.whl (88 kB)\n","\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m88.6/88.6 kB\u001b[0m \u001b[31m1.3 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n","\u001b[?25hRequirement already satisfied: langchain-core<0.3,>=0.2 in /usr/local/lib/python3.10/dist-packages (from langgraph==0.0.66) (0.2.10)\n","Requirement already satisfied: PyYAML>=5.3 in /usr/local/lib/python3.10/dist-packages (from langchain-core<0.3,>=0.2->langgraph==0.0.66) (6.0.1)\n","Requirement already satisfied: jsonpatch<2.0,>=1.33 in /usr/local/lib/python3.10/dist-packages (from langchain-core<0.3,>=0.2->langgraph==0.0.66) (1.33)\n","Requirement already satisfied: langsmith<0.2.0,>=0.1.75 in /usr/local/lib/python3.10/dist-packages (from langchain-core<0.3,>=0.2->langgraph==0.0.66) (0.1.82)\n","Requirement already satisfied: packaging<25,>=23.2 in /usr/local/lib/python3.10/dist-packages (from langchain-core<0.3,>=0.2->langgraph==0.0.66) (24.1)\n","Requirement already satisfied: pydantic<3,>=1 in /usr/local/lib/python3.10/dist-packages (from langchain-core<0.3,>=0.2->langgraph==0.0.66) (2.7.4)\n","Requirement already satisfied: tenacity!=8.4.0,<9.0.0,>=8.1.0 in /usr/local/lib/python3.10/dist-packages (from langchain-core<0.3,>=0.2->langgraph==0.0.66) (8.4.2)\n","Requirement already satisfied: jsonpointer>=1.9 in /usr/local/lib/python3.10/dist-packages (from jsonpatch<2.0,>=1.33->langchain-core<0.3,>=0.2->langgraph==0.0.66) (3.0.0)\n","Requirement already satisfied: orjson<4.0.0,>=3.9.14 in /usr/local/lib/python3.10/dist-packages (from langsmith<0.2.0,>=0.1.75->langchain-core<0.3,>=0.2->langgraph==0.0.66) (3.10.5)\n","Requirement already satisfied: requests<3,>=2 in /usr/local/lib/python3.10/dist-packages (from langsmith<0.2.0,>=0.1.75->langchain-core<0.3,>=0.2->langgraph==0.0.66) (2.31.0)\n","Requirement already satisfied: annotated-types>=0.4.0 in /usr/local/lib/python3.10/dist-packages (from pydantic<3,>=1->langchain-core<0.3,>=0.2->langgraph==0.0.66) (0.7.0)\n","Requirement already satisfied: pydantic-core==2.18.4 in /usr/local/lib/python3.10/dist-packages (from pydantic<3,>=1->langchain-core<0.3,>=0.2->langgraph==0.0.66) (2.18.4)\n","Requirement already satisfied: typing-extensions>=4.6.1 in /usr/local/lib/python3.10/dist-packages (from pydantic<3,>=1->langchain-core<0.3,>=0.2->langgraph==0.0.66) (4.12.2)\n","Requirement already satisfied: charset-normalizer<4,>=2 in /usr/local/lib/python3.10/dist-packages (from requests<3,>=2->langsmith<0.2.0,>=0.1.75->langchain-core<0.3,>=0.2->langgraph==0.0.66) (3.3.2)\n","Requirement already satisfied: idna<4,>=2.5 in /usr/local/lib/python3.10/dist-packages (from requests<3,>=2->langsmith<0.2.0,>=0.1.75->langchain-core<0.3,>=0.2->langgraph==0.0.66) (3.7)\n","Requirement already satisfied: urllib3<3,>=1.21.1 in /usr/local/lib/python3.10/dist-packages (from requests<3,>=2->langsmith<0.2.0,>=0.1.75->langchain-core<0.3,>=0.2->langgraph==0.0.66) (2.0.7)\n","Requirement already satisfied: certifi>=2017.4.17 in /usr/local/lib/python3.10/dist-packages (from requests<3,>=2->langsmith<0.2.0,>=0.1.75->langchain-core<0.3,>=0.2->langgraph==0.0.66) (2024.6.2)\n","Installing collected packages: langgraph\n","Successfully installed langgraph-0.0.66\n"]}]},{"cell_type":"code","source":["pip list"],"metadata":{"colab":{"base_uri":"https://localhost:8080/"},"id":"V9Ddz2XINJP_","executionInfo":{"status":"ok","timestamp":1718531945717,"user_tz":-480,"elapsed":3625,"user":{"displayName":"李辉","userId":"12972001611808140221"}},"outputId":"ad79eccc-7c07-40f8-ef9f-e23122adaf54","collapsed":true},"id":"V9Ddz2XINJP_","execution_count":null,"outputs":[{"output_type":"stream","name":"stdout","text":["Package                          Version\n","-------------------------------- ---------------------\n","absl-py                          1.4.0\n","aiohttp                          3.9.5\n","aiosignal                        1.3.1\n","alabaster                        0.7.16\n","albumentations                   1.3.1\n","altair                           4.2.2\n","annotated-types                  0.7.0\n","anyio                            3.7.1\n","argon2-cffi                      23.1.0\n","argon2-cffi-bindings             21.2.0\n","array_record                     0.5.1\n","arviz                            0.15.1\n","astropy                          5.3.4\n","astunparse                       1.6.3\n","async-timeout                    4.0.3\n","atpublic                         4.1.0\n","attrs                            23.2.0\n","audioread                        3.0.1\n","autograd                         1.6.2\n","Babel                            2.15.0\n","backcall                         0.2.0\n","beautifulsoup4                   4.12.3\n","bidict                           0.23.1\n","bigframes                        1.8.0\n","bleach                           6.1.0\n","blinker                          1.4\n","blis                             0.7.11\n","blosc2                           2.0.0\n","bokeh                            3.3.4\n","bqplot                           0.12.43\n","branca                           0.7.2\n","build                            1.2.1\n","CacheControl                     0.14.0\n","cachetools                       5.3.3\n","catalogue                        2.0.10\n","certifi                          2024.6.2\n","cffi                             1.16.0\n","chardet                          5.2.0\n","charset-normalizer               3.3.2\n","chex                             0.1.86\n","click                            8.1.7\n","click-plugins                    1.1.1\n","cligj                            0.7.2\n","cloudpathlib                     0.18.1\n","cloudpickle                      2.2.1\n","cmake                            3.27.9\n","cmdstanpy                        1.2.3\n","colorcet                         3.1.0\n","colorlover                       0.3.0\n","colour                           0.1.5\n","community                        1.0.0b1\n","confection                       0.1.5\n","cons                             0.4.6\n","contextlib2                      21.6.0\n","contourpy                        1.2.1\n","cryptography                     42.0.8\n","cuda-python                      12.2.1\n","cudf-cu12                        24.4.1\n","cufflinks                        0.17.3\n","cupy-cuda12x                     12.2.0\n","cvxopt                           1.3.2\n","cvxpy                            1.3.4\n","cycler                           0.12.1\n","cymem                            2.0.8\n","Cython                           3.0.10\n","dask                             2023.8.1\n","dataclasses-json                 0.6.7\n","datascience                      0.17.6\n","db-dtypes                        1.2.0\n","dbus-python                      1.2.18\n","debugpy                          1.6.6\n","decorator                        4.4.2\n","defusedxml                       0.7.1\n","distributed                      2023.8.1\n","distro                           1.7.0\n","dlib                             19.24.4\n","dm-tree                          0.1.8\n","docstring_parser                 0.16\n","docutils                         0.18.1\n","dopamine_rl                      4.0.9\n","duckdb                           0.10.3\n","earthengine-api                  0.1.406\n","easydict                         1.13\n","ecos                             2.0.13\n","editdistance                     0.6.2\n","eerepr                           0.0.4\n","en-core-web-sm                   3.7.1\n","entrypoints                      0.4\n","et-xmlfile                       1.1.0\n","etils                            1.7.0\n","etuples                          0.3.9\n","exceptiongroup                   1.2.1\n","faiss-gpu                        1.7.2\n","fastai                           2.7.15\n","fastcore                         1.5.45\n","fastdownload                     0.0.7\n","fastjsonschema                   2.19.1\n","fastprogress                     1.0.3\n","fastrlock                        0.8.2\n","filelock                         3.14.0\n","fiona                            1.9.6\n","firebase-admin                   5.3.0\n","Flask                            2.2.5\n","flatbuffers                      24.3.25\n","flax                             0.8.4\n","folium                           0.14.0\n","fonttools                        4.53.0\n","frozendict                       2.4.4\n","frozenlist                       1.4.1\n","fsspec                           2023.6.0\n","future                           0.18.3\n","gast                             0.5.4\n","gcsfs                            2023.6.0\n","GDAL                             3.6.4\n","gdown                            5.1.0\n","geemap                           0.32.1\n","gensim                           4.3.2\n","geocoder                         1.38.1\n","geographiclib                    2.0\n","geopandas                        0.13.2\n","geopy                            2.3.0\n","gin-config                       0.5.0\n","glob2                            0.7\n","google                           2.0.3\n","google-ai-generativelanguage     0.6.4\n","google-api-core                  2.11.1\n","google-api-python-client         2.84.0\n","google-auth                      2.27.0\n","google-auth-httplib2             0.1.1\n","google-auth-oauthlib             1.2.0\n","google-cloud-aiplatform          1.54.1\n","google-cloud-bigquery            3.21.0\n","google-cloud-bigquery-connection 1.12.1\n","google-cloud-bigquery-storage    2.25.0\n","google-cloud-core                2.3.3\n","google-cloud-datastore           2.15.2\n","google-cloud-firestore           2.11.1\n","google-cloud-functions           1.13.3\n","google-cloud-iam                 2.15.0\n","google-cloud-language            2.13.3\n","google-cloud-resource-manager    1.12.3\n","google-cloud-storage             2.8.0\n","google-cloud-translate           3.11.3\n","google-colab                     1.0.0\n","google-crc32c                    1.5.0\n","google-generativeai              0.5.4\n","google-pasta                     0.2.0\n","google-resumable-media           2.7.0\n","googleapis-common-protos         1.63.1\n","googledrivedownloader            0.4\n","graphviz                         0.20.3\n","greenlet                         3.0.3\n","grpc-google-iam-v1               0.13.0\n","grpcio                           1.64.1\n","grpcio-status                    1.48.2\n","gspread                          6.0.2\n","gspread-dataframe                3.3.1\n","gym                              0.25.2\n","gym-notices                      0.0.8\n","h11                              0.14.0\n","h5netcdf                         1.3.0\n","h5py                             3.9.0\n","holidays                         0.50\n","holoviews                        1.17.1\n","html5lib                         1.1\n","httpcore                         1.0.5\n","httpimport                       1.3.1\n","httplib2                         0.22.0\n","httpx                            0.27.0\n","huggingface-hub                  0.23.3\n","humanize                         4.7.0\n","hyperopt                         0.2.7\n","ibis-framework                   8.0.0\n","idna                             3.7\n","imageio                          2.31.6\n","imageio-ffmpeg                   0.5.1\n","imagesize                        1.4.1\n","imbalanced-learn                 0.10.1\n","imgaug                           0.4.0\n","immutabledict                    4.2.0\n","importlib_metadata               7.1.0\n","importlib_resources              6.4.0\n","imutils                          0.5.4\n","inflect                          7.0.0\n","iniconfig                        2.0.0\n","intel-openmp                     2023.2.4\n","ipyevents                        2.0.2\n","ipyfilechooser                   0.6.0\n","ipykernel                        5.5.6\n","ipyleaflet                       0.18.2\n","ipython                          7.34.0\n","ipython-genutils                 0.2.0\n","ipython-sql                      0.5.0\n","ipytree                          0.2.2\n","ipywidgets                       7.7.1\n","itsdangerous                     2.2.0\n","jax                              0.4.26\n","jaxlib                           0.4.26+cuda12.cudnn89\n","jeepney                          0.7.1\n","jellyfish                        1.0.4\n","jieba                            0.42.1\n","Jinja2                           3.1.4\n","joblib                           1.4.2\n","jsonpatch                        1.33\n","jsonpickle                       3.2.1\n","jsonpointer                      3.0.0\n","jsonschema                       4.19.2\n","jsonschema-specifications        2023.12.1\n","jupyter-client                   6.1.12\n","jupyter-console                  6.1.0\n","jupyter_core                     5.7.2\n","jupyter-server                   1.24.0\n","jupyterlab_pygments              0.3.0\n","jupyterlab_widgets               3.0.11\n","kaggle                           1.6.14\n","kagglehub                        0.2.6\n","keras                            2.15.0\n","keyring                          23.5.0\n","kiwisolver                       1.4.5\n","langchain                        0.2.5\n","langchain-community              0.2.5\n","langchain-core                   0.2.7\n","langchain-openai                 0.1.8\n","langchain-text-splitters         0.2.1\n","langcodes                        3.4.0\n","langgraph                        0.0.66\n","langsmith                        0.1.77\n","language_data                    1.2.0\n","launchpadlib                     1.10.16\n","lazr.restfulclient               0.14.4\n","lazr.uri                         1.0.6\n","lazy_loader                      0.4\n","libclang                         18.1.1\n","librosa                          0.10.2.post1\n","lightgbm                         4.1.0\n","linkify-it-py                    2.0.3\n","llvmlite                         0.41.1\n","locket                           1.0.0\n","logical-unification              0.4.6\n","lxml                             4.9.4\n","malloy                           2023.1067\n","marisa-trie                      1.2.0\n","Markdown                         3.6\n","markdown-it-py                   3.0.0\n","MarkupSafe                       2.1.5\n","marshmallow                      3.21.3\n","matplotlib                       3.7.1\n","matplotlib-inline                0.1.7\n","matplotlib-venn                  0.11.10\n","mdit-py-plugins                  0.4.1\n","mdurl                            0.1.2\n","miniKanren                       1.0.3\n","missingno                        0.5.2\n","mistune                          0.8.4\n","mizani                           0.9.3\n","mkl                              2023.2.0\n","ml-dtypes                        0.2.0\n","mlxtend                          0.22.0\n","more-itertools                   10.1.0\n","moviepy                          1.0.3\n","mpmath                           1.3.0\n","msgpack                          1.0.8\n","multidict                        6.0.5\n","multipledispatch                 1.0.0\n","multitasking                     0.0.11\n","murmurhash                       1.0.10\n","music21                          9.1.0\n","mypy-extensions                  1.0.0\n","natsort                          8.4.0\n","nbclassic                        1.1.0\n","nbclient                         0.10.0\n","nbconvert                        6.5.4\n","nbformat                         5.10.4\n","nest-asyncio                     1.6.0\n","networkx                         3.3\n","nibabel                          4.0.2\n","nltk                             3.8.1\n","notebook                         6.5.5\n","notebook_shim                    0.2.4\n","numba                            0.58.1\n","numexpr                          2.10.0\n","numpy                            1.25.2\n","nvtx                             0.2.10\n","oauth2client                     4.1.3\n","oauthlib                         3.2.2\n","openai                           1.34.0\n","opencv-contrib-python            4.8.0.76\n","opencv-python                    4.8.0.76\n","opencv-python-headless           4.10.0.82\n","openpyxl                         3.1.3\n","opt-einsum                       3.3.0\n","optax                            0.2.2\n","orbax-checkpoint                 0.4.4\n","orjson                           3.10.5\n","osqp                             0.6.2.post8\n","packaging                        24.1\n","pandas                           2.0.3\n","pandas-datareader                0.10.0\n","pandas-gbq                       0.19.2\n","pandas-stubs                     2.0.3.230814\n","pandocfilters                    1.5.1\n","panel                            1.3.8\n","param                            2.1.0\n","parso                            0.8.4\n","parsy                            2.1\n","partd                            1.4.2\n","pathlib                          1.0.1\n","patsy                            0.5.6\n","peewee                           3.17.5\n","pexpect                          4.9.0\n","pickleshare                      0.7.5\n","Pillow                           9.4.0\n","pip                              23.1.2\n","pip-tools                        6.13.0\n","platformdirs                     4.2.2\n","plotly                           5.15.0\n","plotnine                         0.12.4\n","pluggy                           1.5.0\n","polars                           0.20.2\n","pooch                            1.8.2\n","portpicker                       1.5.2\n","prefetch-generator               1.0.3\n","preshed                          3.0.9\n","prettytable                      3.10.0\n","proglog                          0.1.10\n","progressbar2                     4.2.0\n","prometheus_client                0.20.0\n","promise                          2.3\n","prompt_toolkit                   3.0.47\n","prophet                          1.1.5\n","proto-plus                       1.23.0\n","protobuf                         3.20.3\n","psutil                           5.9.5\n","psycopg2                         2.9.9\n","ptyprocess                       0.7.0\n","py-cpuinfo                       9.0.0\n","py4j                             0.10.9.7\n","pyarrow                          14.0.2\n","pyarrow-hotfix                   0.6\n","pyasn1                           0.6.0\n","pyasn1_modules                   0.4.0\n","pycocotools                      2.0.7\n","pycparser                        2.22\n","pydantic                         2.7.3\n","pydantic_core                    2.18.4\n","pydata-google-auth               1.8.2\n","pydot                            1.4.2\n","pydot-ng                         2.0.0\n","pydotplus                        2.0.2\n","PyDrive                          1.3.1\n","PyDrive2                         1.6.3\n","pyerfa                           2.0.1.4\n","pygame                           2.5.2\n","Pygments                         2.16.1\n","PyGObject                        3.42.1\n","PyJWT                            2.3.0\n","pymc                             5.10.4\n","pymystem3                        0.2.0\n","pynvjitlink-cu12                 0.2.3\n","PyOpenGL                         3.1.7\n","pyOpenSSL                        24.1.0\n","pyparsing                        3.1.2\n","pyperclip                        1.8.2\n","pyproj                           3.6.1\n","pyproject_hooks                  1.1.0\n","pyshp                            2.3.1\n","PySocks                          1.7.1\n","pytensor                         2.18.6\n","pytest                           7.4.4\n","python-apt                       0.0.0\n","python-box                       7.1.1\n","python-dateutil                  2.8.2\n","python-louvain                   0.16\n","python-slugify                   8.0.4\n","python-utils                     3.8.2\n","pytz                             2023.4\n","pyviz_comms                      3.0.2\n","PyWavelets                       1.6.0\n","PyYAML                           6.0.1\n","pyzmq                            24.0.1\n","qdldl                            0.1.7.post2\n","qudida                           0.0.4\n","ratelim                          0.1.6\n","referencing                      0.35.1\n","regex                            2024.5.15\n","requests                         2.31.0\n","requests-oauthlib                1.3.1\n","requirements-parser              0.9.0\n","rich                             13.7.1\n","rmm-cu12                         24.4.0\n","rpds-py                          0.18.1\n","rpy2                             3.4.2\n","rsa                              4.9\n","safetensors                      0.4.3\n","scikit-image                     0.19.3\n","scikit-learn                     1.2.2\n","scipy                            1.11.4\n","scooby                           0.10.0\n","scs                              3.2.4.post2\n","seaborn                          0.13.1\n","SecretStorage                    3.3.1\n","Send2Trash                       1.8.3\n","sentencepiece                    0.1.99\n","setuptools                       67.7.2\n","shapely                          2.0.4\n","shellingham                      1.5.4\n","simple_parsing                   0.1.5\n","six                              1.16.0\n","sklearn-pandas                   2.2.0\n","smart-open                       7.0.4\n","sniffio                          1.3.1\n","snowballstemmer                  2.2.0\n","sortedcontainers                 2.4.0\n","soundfile                        0.12.1\n","soupsieve                        2.5\n","soxr                             0.3.7\n","spacy                            3.7.5\n","spacy-legacy                     3.0.12\n","spacy-loggers                    1.0.5\n","Sphinx                           5.0.2\n","sphinxcontrib-applehelp          1.0.8\n","sphinxcontrib-devhelp            1.0.6\n","sphinxcontrib-htmlhelp           2.0.5\n","sphinxcontrib-jsmath             1.0.1\n","sphinxcontrib-qthelp             1.0.7\n","sphinxcontrib-serializinghtml    1.1.10\n","SQLAlchemy                       2.0.30\n","sqlglot                          20.11.0\n","sqlparse                         0.5.0\n","srsly                            2.4.8\n","stanio                           0.5.0\n","statsmodels                      0.14.2\n","StrEnum                          0.4.15\n","sympy                            1.12.1\n","tables                           3.8.0\n","tabulate                         0.9.0\n","tbb                              2021.12.0\n","tblib                            3.0.0\n","tenacity                         8.3.0\n","tensorboard                      2.15.2\n","tensorboard-data-server          0.7.2\n","tensorflow                       2.15.0\n","tensorflow-datasets              4.9.6\n","tensorflow-estimator             2.15.0\n","tensorflow-gcs-config            2.15.0\n","tensorflow-hub                   0.16.1\n","tensorflow-io-gcs-filesystem     0.37.0\n","tensorflow-metadata              1.15.0\n","tensorflow-probability           0.23.0\n","tensorstore                      0.1.45\n","termcolor                        2.4.0\n","terminado                        0.18.1\n","text-unidecode                   1.3\n","textblob                         0.17.1\n","tf_keras                         2.15.1\n","tf-slim                          1.1.0\n","thinc                            8.2.4\n","threadpoolctl                    3.5.0\n","tifffile                         2024.5.22\n","tiktoken                         0.7.0\n","tinycss2                         1.3.0\n","tokenizers                       0.19.1\n","toml                             0.10.2\n","tomli                            2.0.1\n","toolz                            0.12.1\n","torch                            2.3.0+cu121\n","torchaudio                       2.3.0+cu121\n","torchsummary                     1.5.1\n","torchtext                        0.18.0\n","torchvision                      0.18.0+cu121\n","tornado                          6.3.3\n","tqdm                             4.66.4\n","traitlets                        5.7.1\n","traittypes                       0.2.1\n","transformers                     4.41.2\n","triton                           2.3.0\n","tweepy                           4.14.0\n","typer                            0.12.3\n","types-pytz                       2024.1.0.20240417\n","types-setuptools                 70.0.0.20240524\n","typing_extensions                4.12.2\n","typing-inspect                   0.9.0\n","tzdata                           2024.1\n","tzlocal                          5.2\n","uc-micro-py                      1.0.3\n","uritemplate                      4.1.1\n","urllib3                          2.0.7\n","vega-datasets                    0.9.0\n","wadllib                          1.3.6\n","wasabi                           1.1.3\n","wcwidth                          0.2.13\n","weasel                           0.4.1\n","webcolors                        24.6.0\n","webencodings                     0.5.1\n","websocket-client                 1.8.0\n","Werkzeug                         3.0.3\n","wheel                            0.43.0\n","widgetsnbextension               3.6.6\n","wordcloud                        1.9.3\n","wrapt                            1.14.1\n","xarray                           2023.7.0\n","xarray-einstats                  0.7.0\n","xgboost                          2.0.3\n","xlrd                             2.0.1\n","xyzservices                      2024.6.0\n","yarl                             1.9.4\n","yellowbrick                      1.5\n","yfinance                         0.2.40\n","zict                             3.0.0\n","zipp                             3.19.2\n"]}]},{"cell_type":"markdown","id":"7d0b13e3","metadata":{"id":"7d0b13e3"},"source":["在这个教程中我们将使用openai的模型"]},{"cell_type":"code","execution_count":18,"id":"45d2602c","metadata":{"id":"45d2602c","executionInfo":{"status":"ok","timestamp":1719880533226,"user_tz":-480,"elapsed":3867,"user":{"displayName":"李辉","userId":"12972001611808140221"}}},"outputs":[],"source":["import os\n","from google.colab import userdata\n","os.environ[\"OPENAI_API_KEY\"] = userdata.get('MOON_API_KAY')\n","os.environ[\"OPENAI_API_BASE\"] = userdata.get('MOON_API_BASE')\n","os.environ[\"LANGCHAIN_TRACING_V2\"] = \"true\"\n","os.environ[\"LANGCHAIN_API_KEY\"] = userdata.get('LANGCHAIN_API_KEY')"]},{"cell_type":"markdown","source":["创建sqlite数据库"],"metadata":{"id":"5xJXzBE2149S"},"id":"5xJXzBE2149S"},{"cell_type":"code","execution_count":4,"id":"w-HGd_w--G-l","metadata":{"colab":{"base_uri":"https://localhost:8080/"},"executionInfo":{"elapsed":1506,"status":"ok","timestamp":1719879934323,"user":{"displayName":"李辉","userId":"12972001611808140221"},"user_tz":-480},"id":"w-HGd_w--G-l","outputId":"75ddbb55-1480-4393-83ef-dc8fcae85f52"},"outputs":[{"output_type":"stream","name":"stdout","text":["Downloaded Chinook_Sqlite.sql successfully.\n","Executed SQL script and created database successfully.\n","(1, 'AC/DC')\n","(2, 'Accept')\n","(3, 'Aerosmith')\n","(4, 'Alanis Morissette')\n","(5, 'Alice In Chains')\n","(6, 'Antônio Carlos Jobim')\n","(7, 'Apocalyptica')\n","(8, 'Audioslave')\n","(9, 'BackBeat')\n","(10, 'Billy Cobham')\n"]}],"source":["import sqlite3\n","import requests\n","\n","# Step 1: 下载 Chinook_Sqlite.sql 文件\n","url = \"https://raw.githubusercontent.com/lerocha/chinook-database/master/ChinookDatabase/DataSources/Chinook_Sqlite.sql\"\n","sql_file_path = \"Chinook_Sqlite.sql\"\n","\n","try:\n","    response = requests.get(url)\n","    response.raise_for_status()  # Check if the download was successful\n","    with open(sql_file_path, \"wb\") as file:\n","        file.write(response.content)\n","    print(\"Downloaded Chinook_Sqlite.sql successfully.\")\n","except requests.exceptions.RequestException as e:\n","    print(f\"Failed to download file: {e}\")\n","\n","# Step 2: 创建连接数据库 Chinook.db\n","db_path = \"Chinook.db\"\n","conn = sqlite3.connect(db_path)\n","cursor = conn.cursor()\n","\n","# Step 3: 读取执行 Chinook_Sqlite.sql 文件\n","try:\n","    with open(sql_file_path, \"r\", encoding=\"utf-8\") as file:\n","        sql_script = file.read()\n","    cursor.executescript(sql_script)\n","    conn.commit()\n","    print(\"Executed SQL script and created database successfully.\")\n","except Exception as e:\n","    print(f\"Failed to execute SQL script: {e}\")\n","\n","# Step 4: 查询测试\n","try:\n","    cursor.execute(\"SELECT * FROM Artist LIMIT 10;\")\n","    rows = cursor.fetchall()\n","    for row in rows:\n","        print(row)\n","except Exception as e:\n","    print(f\"Failed to run test query: {e}\")\n","\n","# 关闭连接\n","conn.close()\n"]},{"cell_type":"markdown","id":"9760231a","metadata":{"id":"9760231a"},"source":["我们来看一下使用SQLite连接Chinook数据库，请按照以下安装步骤，在与此笔记本相同的目录中创建Chinook.db：\n","\n","- 下载保存文件到 `Chinook.sql`\n","- 执行 `sqlite3 Chinook.db` 命令\n","- 执行 `.read Chinook.sql` 命令\n","- 测试查询`SELECT * FROM Artist LIMIT 10;`\n","\n","现在， `Chinook.db` 已经在我们的目录中，我们可以使用由SQLAlchemy驱动的SQLDatabase类与之交互："]},{"cell_type":"code","execution_count":5,"id":"e8e6ce3c","metadata":{"colab":{"base_uri":"https://localhost:8080/","height":69},"executionInfo":{"elapsed":1237,"status":"ok","timestamp":1719879940446,"user":{"displayName":"李辉","userId":"12972001611808140221"},"user_tz":-480},"id":"e8e6ce3c","outputId":"50bed8ad-02a9-4904-ff5a-49eef5515b40"},"outputs":[{"output_type":"stream","name":"stdout","text":["sqlite\n","['Album', 'Artist', 'Customer', 'Employee', 'Genre', 'Invoice', 'InvoiceLine', 'MediaType', 'Playlist', 'PlaylistTrack', 'Track']\n"]},{"output_type":"execute_result","data":{"text/plain":["'[(6,)]'"],"application/vnd.google.colaboratory.intrinsic+json":{"type":"string"}},"metadata":{},"execution_count":5}],"source":["from langchain_community.utilities import SQLDatabase\n","db = SQLDatabase.from_uri(\"sqlite:///Chinook.db\")\n","print(db.dialect)\n","print(db.get_usable_table_names())\n","db.run(\"SELECT ArtistId FROM Artist WHERE Name = 'Antônio Carlos Jobim'\")"]},{"cell_type":"markdown","id":"ba263a0f","metadata":{"id":"ba263a0f"},"source":["**API 调用:**[SQLDatabase](https://api.python.langchain.com/en/latest/utilities/langchain_community.utilities.sql_database.SQLDatabase.html)\n","\n","\n","现在我们已经有了一个可以查询的 SQL 数据库。现在让我们尝试将它连接到一个 LLM。\n","\n","## Chains\n","\n","链（也就是由LangChain的[Runnables](https://python.langchain.com/v0.2/docs/concepts/#langchain-expression-language)子类组成的组合）适合能够确定执行流程的应用。我们可以先创建一个简单的链，它接受一个问题并执行以下操作：\n","\n","- 将问题转换成SQL查询语句；\n","- 执行这个查询；\n","- 用查询结果来回答最初的问题。\n","\n","但是链并不支持所有场景。比如，对于任何用户输入哪怕是“hello”，这个系统都会执行一个SQL查询。重要的是，有些问题需要多次查询才能解答。这种场景适用于agent智能体。\n","\n","### 将问题转换为 SQL 查询语句\n","\n","\n","在链或agent中，第一步是获取用户输入并将其转换为SQL查询。LangChain提供了一个内置的链来完成这项工作： `create_sql_query_chain`。"]},{"cell_type":"code","execution_count":19,"id":"c8b7c86f","metadata":{"id":"c8b7c86f","executionInfo":{"status":"ok","timestamp":1719880564675,"user_tz":-480,"elapsed":397,"user":{"displayName":"李辉","userId":"12972001611808140221"}}},"outputs":[],"source":["from langchain_openai import ChatOpenAI\n","llm = ChatOpenAI(model=\"moonshot-v1-32k\")"]},{"cell_type":"code","execution_count":20,"id":"08b83ceb","metadata":{"colab":{"base_uri":"https://localhost:8080/","height":35},"executionInfo":{"elapsed":3349,"status":"ok","timestamp":1719880570609,"user":{"displayName":"李辉","userId":"12972001611808140221"},"user_tz":-480},"id":"08b83ceb","outputId":"e6ce0f2b-b4ad-4a04-9b3e-940180c97650"},"outputs":[{"output_type":"execute_result","data":{"text/plain":["'SELECT COUNT(*) \\nFROM \"Employee\";'"],"application/vnd.google.colaboratory.intrinsic+json":{"type":"string"}},"metadata":{},"execution_count":20}],"source":["from langchain.chains import create_sql_query_chain\n","chain = create_sql_query_chain(llm, db)\n","response = chain.invoke({\"question\": \"How many employees are there\"})\n","response"]},{"cell_type":"markdown","id":"d0c28573","metadata":{"id":"d0c28573"},"source":["**API 调用:**[create_sql_query_chain](https://api.python.langchain.com/en/latest/chains/langchain.chains.sql_database.query.create_sql_query_chain.html)\n","\n","尝试执行sql\n","\n"]},{"cell_type":"code","execution_count":21,"id":"SwdSIpbaATEf","metadata":{"colab":{"base_uri":"https://localhost:8080/","height":35},"executionInfo":{"elapsed":400,"status":"ok","timestamp":1719880575625,"user":{"displayName":"李辉","userId":"12972001611808140221"},"user_tz":-480},"id":"SwdSIpbaATEf","outputId":"fae92f79-c414-4be8-a20f-194bd5db5921"},"outputs":[{"output_type":"execute_result","data":{"text/plain":["'[(8,)]'"],"application/vnd.google.colaboratory.intrinsic+json":{"type":"string"}},"metadata":{},"execution_count":21}],"source":["db.run(response)"]},{"cell_type":"markdown","id":"UGyoyp1DASx9","metadata":{"id":"UGyoyp1DASx9"},"source":["\n","我们可以查看[LangSmith追踪](https://smith.langchain.com/public/c8fa52ea-be46-4829-bde2-52894970b830/r)来更好地理解这个链在做什么。我们也可以直接查看提示词（如下）：\n","\n","- 明确引用了SQLite。\n","- 为所有可用的表提供了定义。\n","- 为每个表提供了三行示例数据。\n","\n","我们也可以像这样检查完整的提示："]},{"cell_type":"code","execution_count":22,"id":"d4f493c4","metadata":{"colab":{"base_uri":"https://localhost:8080/"},"executionInfo":{"elapsed":407,"status":"ok","timestamp":1719880583952,"user":{"displayName":"李辉","userId":"12972001611808140221"},"user_tz":-480},"id":"d4f493c4","outputId":"855419a3-7343-4ee7-e4b0-d674cd2d13cf"},"outputs":[{"output_type":"stream","name":"stdout","text":["You are a SQLite expert. Given an input question, first create a syntactically correct SQLite query to run, then look at the results of the query and return the answer to the input question.\n","Unless the user specifies in the question a specific number of examples to obtain, query for at most 5 results using the LIMIT clause as per SQLite. You can order the results to return the most informative data in the database.\n","Never query for all columns from a table. You must query only the columns that are needed to answer the question. Wrap each column name in double quotes (\") to denote them as delimited identifiers.\n","Pay attention to use only the column names you can see in the tables below. Be careful to not query for columns that do not exist. Also, pay attention to which column is in which table.\n","Pay attention to use date('now') function to get the current date, if the question involves \"today\".\n","\n","Use the following format:\n","\n","Question: Question here\n","SQLQuery: SQL Query to run\n","SQLResult: Result of the SQLQuery\n","Answer: Final answer here\n","\n","Only use the following tables:\n","\u001b[33;1m\u001b[1;3m{table_info}\u001b[0m\n","\n","Question: \u001b[33;1m\u001b[1;3m{input}\u001b[0m\n"]}],"source":["chain.get_prompts()[0].pretty_print()"]},{"cell_type":"markdown","id":"ea82a3fc","metadata":{"id":"ea82a3fc"},"source":["\n","\n","### 执行 SQL 查询\n","\n","生成了SQL查询之后，下一步自然是执行它。这是创建SQL链中最危险的一部分。请仔细考虑是否适合在您的数据上运行自动化查询。尽可能地最小化数据库连接权限。可以考虑在执行查询之前为您的链添加人工审批步骤（见下文）。\n","\n","我们可以使用 `QuerySQLDatabaseTool` 轻松地为链添加执行sql的节点："]},{"cell_type":"code","execution_count":null,"id":"b5b4fbdd","metadata":{"colab":{"base_uri":"https://localhost:8080/","height":35},"executionInfo":{"elapsed":1578,"status":"ok","timestamp":1718530524444,"user":{"displayName":"李辉","userId":"12972001611808140221"},"user_tz":-480},"id":"b5b4fbdd","outputId":"72ddbda7-aa1e-4fb4-a0f0-6664aef68058"},"outputs":[{"output_type":"execute_result","data":{"text/plain":["'[(8,)]'"],"application/vnd.google.colaboratory.intrinsic+json":{"type":"string"}},"metadata":{},"execution_count":13}],"source":["from langchain_community.tools.sql_database.tool import QuerySQLDataBaseTool\n","execute_query = QuerySQLDataBaseTool(db=db)\n","write_query = create_sql_query_chain(llm, db)\n","chain = write_query | execute_query\n","chain.invoke({\"question\": \"How many employees are there\"})"]},{"cell_type":"markdown","id":"dcb1dc8d","metadata":{"id":"dcb1dc8d"},"source":["**API 调用:**[QuerySQLDataBaseTool](https://api.python.langchain.com/en/latest/tools/langchain_community.tools.sql_database.tool.QuerySQLDataBaseTool.html)\n","\n","```\n","'[(8,)]'\n","```\n","\n","### 回答问题\n","\n","现在我们已经能够自动生成并执行查询，只需要将原始问题和SQL查询结果结合起来，生成最终答案。我们可以通过再次将问题和结果传递给大型语言模型(LLM)来实现："]},{"cell_type":"code","execution_count":null,"id":"df37d6d0","metadata":{"colab":{"base_uri":"https://localhost:8080/","height":35},"executionInfo":{"elapsed":2729,"status":"ok","timestamp":1718530852710,"user":{"displayName":"李辉","userId":"12972001611808140221"},"user_tz":-480},"id":"df37d6d0","outputId":"ca74f9b7-ad37-4cc9-c9aa-b140fe0085fb"},"outputs":[{"output_type":"execute_result","data":{"text/plain":["'There are 8 employees in the database.'"],"application/vnd.google.colaboratory.intrinsic+json":{"type":"string"}},"metadata":{},"execution_count":14}],"source":["from operator import itemgetter\n","from langchain_core.output_parsers import StrOutputParser\n","from langchain_core.prompts import PromptTemplate\n","from langchain_core.runnables import RunnablePassthrough\n","answer_prompt = PromptTemplate.from_template(\n","\"\"\"Given the following user question, corresponding SQL query, and SQL result, answer the user question.\n","Question: {question}\n","SQL Query: {query}\n","SQL Result: {result}\n","Answer: \"\"\"\n",")\n","chain = (\n","    RunnablePassthrough.assign(query=write_query).assign(\n","        result=itemgetter(\"query\") | execute_query\n",")\n","| answer_prompt\n","| llm\n","| StrOutputParser()\n",")\n","chain.invoke({\"question\": \"How many employees are there\"})"]},{"cell_type":"markdown","id":"03f4446e","metadata":{"id":"03f4446e"},"source":["**API 调用：**[StrOutputParser](https://api.python.langchain.com/en/latest/output_parsers/langchain_core.output_parsers.string.StrOutputParser.html) | [PromptTemplate](https://api.python.langchain.com/en/latest/prompts/langchain_core.prompts.prompt.PromptTemplate.html) | [RunnablePassthrough](https://api.python.langchain.com/en/latest/runnables/langchain_core.runnables.passthrough.RunnablePassthrough.html)\n","\n","\n","\n","让我们回顾一下上述LCEL中发生的事情。假设这个链被调用。\n","\n","- 在第一个`RunnablePassthrough.assign`之后，会返回一个包含两个元素的可运行对象：`{\"question\": question, \"query\": write_query.invoke(question)}`，其中`write_query`将生成一个SQL查询语句，以回答这个问题。\n","- 在第二个`RunnablePassthrough.assign`之后，我们添加了第三个元素`\"result\"`，它包含`execute_query.invoke(query)`，其中`query`是前一步生成的结果。\n","- 这三个输入被格式化成提示，并传递给LLM。\n","- `StrOutputParser()`提取输出消息的字符串内容。\n","\n","在将大型语言模型（LLMs）、工具、提示和其他链组合在一起时，由于每个都实现了Runnable接口，它们的输入和输出可以向下绑定。\n","\n","### 后续步骤\n","\n","对于更复杂的查询生成，我们可能想要创建少量样本提示或添加查询检查步骤。对于更多内容，请查看：\n","\n","- [提示策略](https://python.langchain.com/v0.2/docs/how_to/sql_prompting/)：高级提示工程技巧。\n","- [查询检查](https://python.langchain.com/v0.2/docs/how_to/sql_query_checking/)：增加查询验证和错误处理。\n","- [大型数据库](https://python.langchain.com/v0.2/docs/how_to/sql_large_db/)：处理大型数据库的技术\n","\n"]},{"cell_type":"markdown","source":["## Agents\n","\n","LangChain 有一个 SQL Agent，它提供了一种比链式操作更灵活的与 SQL 数据库交互的方式。使用 SQL Agent 的主要优势包括：\n","\n","- 它可以根据数据库的模式以及数据库的内容（比如描述一个特定的表）来回答问题。\n","- 它可以通过运行生成的查询，捕获追踪栈并正确地重新生成，来从错误中恢复。\n","- 它可以根据需要多次查询数据库以回答用户的问题。\n","- 它仅从相关表中检索的方式来节省令牌。\n","\n","为了初始化agent ，我们将使用 `SQLDatabaseToolkit` 来创建一系列工具：\n","\n","- 创建和执行查询\n","- 检查查询语法\n","- 检索表描述\n","- ...以及更多功能"],"metadata":{"id":"UIAlDssKLoWk"},"id":"UIAlDssKLoWk"},{"cell_type":"code","execution_count":23,"id":"6a736e85","metadata":{"colab":{"base_uri":"https://localhost:8080/"},"executionInfo":{"elapsed":399,"status":"ok","timestamp":1719880607745,"user":{"displayName":"李辉","userId":"12972001611808140221"},"user_tz":-480},"id":"6a736e85","outputId":"4d3213ea-88ac-43d8-b8dd-e1f8c1e96dca"},"outputs":[{"output_type":"execute_result","data":{"text/plain":["[QuerySQLDataBaseTool(description=\"Input to this tool is a detailed and correct SQL query, output is a result from the database. If the query is not correct, an error message will be returned. If an error is returned, rewrite the query, check the query, and try again. If you encounter an issue with Unknown column 'xxxx' in 'field list', use sql_db_schema to query the correct table fields.\", db=<langchain_community.utilities.sql_database.SQLDatabase object at 0x7f1cc3bf9630>),\n"," InfoSQLDatabaseTool(description='Input to this tool is a comma-separated list of tables, output is the schema and sample rows for those tables. Be sure that the tables actually exist by calling sql_db_list_tables first! Example Input: table1, table2, table3', db=<langchain_community.utilities.sql_database.SQLDatabase object at 0x7f1cc3bf9630>),\n"," ListSQLDatabaseTool(db=<langchain_community.utilities.sql_database.SQLDatabase object at 0x7f1cc3bf9630>),\n"," QuerySQLCheckerTool(description='Use this tool to double check if your query is correct before executing it. Always use this tool before executing a query with sql_db_query!', db=<langchain_community.utilities.sql_database.SQLDatabase object at 0x7f1cc3bf9630>, llm=ChatOpenAI(client=<openai.resources.chat.completions.Completions object at 0x7f1cc1c63e80>, async_client=<openai.resources.chat.completions.AsyncCompletions object at 0x7f1cdc324940>, model_name='moonshot-v1-32k', openai_api_key=SecretStr('**********'), openai_api_base='https://api.moonshot.cn/v1', openai_proxy=''), llm_chain=LLMChain(prompt=PromptTemplate(input_variables=['dialect', 'query'], template='\\n{query}\\nDouble check the {dialect} query above for common mistakes, including:\\n- Using NOT IN with NULL values\\n- Using UNION when UNION ALL should have been used\\n- Using BETWEEN for exclusive ranges\\n- Data type mismatch in predicates\\n- Properly quoting identifiers\\n- Using the correct number of arguments for functions\\n- Casting to the correct data type\\n- Using the proper columns for joins\\n\\nIf there are any of the above mistakes, rewrite the query. If there are no mistakes, just reproduce the original query.\\n\\nOutput the final SQL query only.\\n\\nSQL Query: '), llm=ChatOpenAI(client=<openai.resources.chat.completions.Completions object at 0x7f1cc1c63e80>, async_client=<openai.resources.chat.completions.AsyncCompletions object at 0x7f1cdc324940>, model_name='moonshot-v1-32k', openai_api_key=SecretStr('**********'), openai_api_base='https://api.moonshot.cn/v1', openai_proxy='')))]"]},"metadata":{},"execution_count":23}],"source":["from langchain_community.agent_toolkits import SQLDatabaseToolkit\n","toolkit = SQLDatabaseToolkit(db=db, llm=llm)\n","tools = toolkit.get_tools()\n","tools"]},{"cell_type":"markdown","id":"d0c1e93f","metadata":{"id":"d0c1e93f"},"source":["**API 调用：**[SQLDatabaseToolkit](https://api.python.langchain.com/en/latest/agent_toolkits/langchain_community.agent_toolkits.sql.toolkit.SQLDatabaseToolkit.html)\n","\n","\n","### System Prompt 系统提示词\n","\n","我们还需要为agent创建一个系统提示词。这将包括如何执行的指令。\n","\n","\n"]},{"cell_type":"code","execution_count":24,"id":"266LmdHtDB0M","metadata":{"executionInfo":{"elapsed":402,"status":"ok","timestamp":1719880612663,"user":{"displayName":"李辉","userId":"12972001611808140221"},"user_tz":-480},"id":"266LmdHtDB0M"},"outputs":[],"source":["from langchain_core.messages import SystemMessage\n","SQL_PREFIX = \"\"\"You are an agent designed to interact with a SQL database.\n","Given an input question, create a syntactically correct SQLite query to run, then look at the results of the query and return the answer.\n","Unless the user specifies a specific number of examples they wish to obtain, always limit your query to at most 5 results.\n","You can order the results by a relevant column to return the most interesting examples in the database.\n","Never query for all the columns from a specific table, only ask for the relevant columns given the question.\n","You have access to tools for interacting with the database.\n","Only use the below tools. Only use the information returned by the below tools to construct your final answer.\n","You MUST double check your query before executing it. If you get an error while executing a query, rewrite the query and try again.\n","DO NOT make any DML statements (INSERT, UPDATE, DELETE, DROP etc.) to the database.\n","To start you should ALWAYS look at the tables in the database to see what you can query.\n","Do NOT skip this step.\n","Then you should query the schema of the most relevant tables.\"\"\"\n","system_message = SystemMessage(content=SQL_PREFIX)"]},{"cell_type":"markdown","id":"7effc51c","metadata":{"id":"7effc51c"},"source":["**API 参考:**[SystemMessage](https://api.python.langchain.com/en/latest/messages/langchain_core.messages.system.SystemMessage.html)\n","\n","### 智能体 agent 初始化\n","我们将使用预构建的[LangGraph](https://python.langchain.com/v0.2/docs/concepts/#langgraph) agent来构建我们的代理。"]},{"cell_type":"code","execution_count":25,"id":"26acfbba","metadata":{"executionInfo":{"elapsed":438,"status":"ok","timestamp":1719880617366,"user":{"displayName":"李辉","userId":"12972001611808140221"},"user_tz":-480},"id":"26acfbba"},"outputs":[],"source":["from langchain_core.messages import HumanMessage\n","from langgraph.prebuilt import create_react_agent\n","agent_executor = create_react_agent(llm, tools, messages_modifier=system_message)"]},{"cell_type":"markdown","id":"31d74404","metadata":{"id":"31d74404"},"source":["**API 调用：**[HumanMessage](https://api.python.langchain.com/en/latest/messages/langchain_core.messages.human.HumanMessage.html)"]},{"cell_type":"code","execution_count":27,"id":"af37b30a","metadata":{"colab":{"base_uri":"https://localhost:8080/"},"executionInfo":{"elapsed":24450,"status":"ok","timestamp":1719880954504,"user":{"displayName":"李辉","userId":"12972001611808140221"},"user_tz":-480},"id":"af37b30a","outputId":"fae2e94c-509d-4a36-ded7-7cdbe9086c56"},"outputs":[{"output_type":"stream","name":"stdout","text":["{'agent': {'messages': [AIMessage(content='', additional_kwargs={'tool_calls': [{'id': 'sql_db_list_tables:0', 'function': {'arguments': '{}', 'name': 'sql_db_list_tables'}, 'type': 'function', 'index': 0}]}, response_metadata={'token_usage': {'completion_tokens': 3, 'prompt_tokens': 568, 'total_tokens': 571}, 'model_name': 'moonshot-v1-32k', 'system_fingerprint': None, 'finish_reason': 'tool_calls', 'logprobs': None}, id='run-2dac0ff8-70dc-44f8-95a3-ed7941ee6d1e-0', tool_calls=[{'name': 'sql_db_list_tables', 'args': {}, 'id': 'sql_db_list_tables:0'}], usage_metadata={'input_tokens': 568, 'output_tokens': 3, 'total_tokens': 571})]}}\n","----\n","{'tools': {'messages': [ToolMessage(content='Album, Artist, Customer, Employee, Genre, Invoice, InvoiceLine, MediaType, Playlist, PlaylistTrack, Track', name='sql_db_list_tables', tool_call_id='sql_db_list_tables:0')]}}\n","----\n","{'agent': {'messages': [AIMessage(content='', additional_kwargs={'tool_calls': [{'id': 'sql_db_schema:0', 'function': {'arguments': '{\\n    \"table_names\": \"Invoice\"\\n}', 'name': 'sql_db_schema'}, 'type': 'function', 'index': 0}]}, response_metadata={'token_usage': {'completion_tokens': 13, 'prompt_tokens': 624, 'total_tokens': 637}, 'model_name': 'moonshot-v1-32k', 'system_fingerprint': None, 'finish_reason': 'tool_calls', 'logprobs': None}, id='run-49642541-88b7-43c5-b02e-e8527c585534-0', tool_calls=[{'name': 'sql_db_schema', 'args': {'table_names': 'Invoice'}, 'id': 'sql_db_schema:0'}], usage_metadata={'input_tokens': 624, 'output_tokens': 13, 'total_tokens': 637})]}}\n","----\n","{'tools': {'messages': [ToolMessage(content='\\nCREATE TABLE \"Invoice\" (\\n\\t\"InvoiceId\" INTEGER NOT NULL, \\n\\t\"CustomerId\" INTEGER NOT NULL, \\n\\t\"InvoiceDate\" DATETIME NOT NULL, \\n\\t\"BillingAddress\" NVARCHAR(70), \\n\\t\"BillingCity\" NVARCHAR(40), \\n\\t\"BillingState\" NVARCHAR(40), \\n\\t\"BillingCountry\" NVARCHAR(40), \\n\\t\"BillingPostalCode\" NVARCHAR(10), \\n\\t\"Total\" NUMERIC(10, 2) NOT NULL, \\n\\tPRIMARY KEY (\"InvoiceId\"), \\n\\tFOREIGN KEY(\"CustomerId\") REFERENCES \"Customer\" (\"CustomerId\")\\n)\\n\\n/*\\n3 rows from Invoice table:\\nInvoiceId\\tCustomerId\\tInvoiceDate\\tBillingAddress\\tBillingCity\\tBillingState\\tBillingCountry\\tBillingPostalCode\\tTotal\\n1\\t2\\t2021-01-01 00:00:00\\tTheodor-Heuss-Straße 34\\tStuttgart\\tNone\\tGermany\\t70174\\t1.98\\n2\\t4\\t2021-01-02 00:00:00\\tUllevålsveien 14\\tOslo\\tNone\\tNorway\\t0171\\t3.96\\n3\\t8\\t2021-01-03 00:00:00\\tGrétrystraat 63\\tBrussels\\tNone\\tBelgium\\t1000\\t5.94\\n*/', name='sql_db_schema', tool_call_id='sql_db_schema:0')]}}\n","----\n","{'agent': {'messages': [AIMessage(content='', additional_kwargs={'tool_calls': [{'id': 'sql_db_query_checker:0', 'function': {'arguments': '{\\n  \"query\": \"SELECT BillingCountry, SUM(Total) AS TotalSpent FROM Invoice GROUP BY BillingCountry ORDER BY TotalSpent DESC LIMIT 5\"\\n}', 'name': 'sql_db_query_checker'}, 'type': 'function', 'index': 0}]}, response_metadata={'token_usage': {'completion_tokens': 38, 'prompt_tokens': 977, 'total_tokens': 1015}, 'model_name': 'moonshot-v1-32k', 'system_fingerprint': None, 'finish_reason': 'tool_calls', 'logprobs': None}, id='run-3faddabc-b54a-4986-9f16-6904145feb05-0', tool_calls=[{'name': 'sql_db_query_checker', 'args': {'query': 'SELECT BillingCountry, SUM(Total) AS TotalSpent FROM Invoice GROUP BY BillingCountry ORDER BY TotalSpent DESC LIMIT 5'}, 'id': 'sql_db_query_checker:0'}], usage_metadata={'input_tokens': 977, 'output_tokens': 38, 'total_tokens': 1015})]}}\n","----\n","{'tools': {'messages': [ToolMessage(content='The provided SQLite query does not seem to have any of the common mistakes listed. The query selects the `BillingCountry` and the sum of the `Total` amounts as `TotalSpent` from the `Invoice` table, groups the results by `BillingCountry`, orders them in descending order by the total spent, and limits the output to the top 5 countries with the highest total spent.\\n\\nHowever, I will double-check the query for any possible issues:\\n\\n1. Using NOT IN with NULL values: The query does not use the `NOT IN` operator.\\n2. Using UNION when UNION ALL should have been used: The query does not use `UNION` or `UNION ALL`.\\n3. Using BETWEEN for exclusive ranges: The query does not use the `BETWEEN` operator.\\n4. Data type mismatch in predicates: The query does not have any predicates that might cause a data type mismatch.\\n5. Properly quoting identifiers: The identifiers are not quoted, but they do not contain any special characters or spaces, so quoting is not necessary.\\n6. Using the correct number of arguments for functions: The `SUM` function is used correctly with one argument.\\n7. Casting to the correct data type: There is no casting in the query.\\n8. Using the proper columns for joins: The query does not perform any joins.\\n\\nSince there are no mistakes, the original query is correct:\\n\\n```sql\\nSELECT BillingCountry, SUM(Total) AS TotalSpent FROM Invoice GROUP BY BillingCountry ORDER BY TotalSpent DESC LIMIT 5\\n```', name='sql_db_query_checker', tool_call_id='sql_db_query_checker:0')]}}\n","----\n","{'agent': {'messages': [AIMessage(content='', additional_kwargs={'tool_calls': [{'id': 'sql_db_query:0', 'function': {'arguments': '{\\n  \"query\": \"SELECT BillingCountry, SUM(Total) AS TotalSpent FROM Invoice GROUP BY BillingCountry ORDER BY TotalSpent DESC LIMIT 5\"\\n}', 'name': 'sql_db_query'}, 'type': 'function', 'index': 0}]}, response_metadata={'token_usage': {'completion_tokens': 38, 'prompt_tokens': 1360, 'total_tokens': 1398}, 'model_name': 'moonshot-v1-32k', 'system_fingerprint': None, 'finish_reason': 'tool_calls', 'logprobs': None}, id='run-2e417260-1d3a-41b2-ae12-846ace5cd445-0', tool_calls=[{'name': 'sql_db_query', 'args': {'query': 'SELECT BillingCountry, SUM(Total) AS TotalSpent FROM Invoice GROUP BY BillingCountry ORDER BY TotalSpent DESC LIMIT 5'}, 'id': 'sql_db_query:0'}], usage_metadata={'input_tokens': 1360, 'output_tokens': 38, 'total_tokens': 1398})]}}\n","----\n","{'tools': {'messages': [ToolMessage(content=\"[('USA', 523.0600000000003), ('Canada', 303.9599999999999), ('France', 195.09999999999994), ('Brazil', 190.09999999999997), ('Germany', 156.48)]\", name='sql_db_query', tool_call_id='sql_db_query:0')]}}\n","----\n","{'agent': {'messages': [AIMessage(content='The country whose customers spent the most is the USA, with a total expenditure of approximately 523.06. Following the USA, Canada has the second-highest expenditure at 303.96, France at 195.10, Brazil at 190.10, and Germany at 156.48. These figures represent the top 5 countries with the highest total spent based on the data from the Invoice table.', response_metadata={'token_usage': {'completion_tokens': 87, 'prompt_tokens': 1499, 'total_tokens': 1586}, 'model_name': 'moonshot-v1-32k', 'system_fingerprint': None, 'finish_reason': 'stop', 'logprobs': None}, id='run-57ef2f8c-7bec-401c-b92f-73e972fde905-0', usage_metadata={'input_tokens': 1499, 'output_tokens': 87, 'total_tokens': 1586})]}}\n","----\n"]}],"source":["for s in agent_executor.stream(\n","  {\"messages\": [HumanMessage(content=\"Which country's customers spent the most?\")]}\n","):\n","  print(s)\n","  print(\"----\")"]},{"cell_type":"markdown","id":"c4bee8f6","metadata":{"id":"c4bee8f6"},"source":["\n","请注意，agent会执行多个查询，直到它获得所需的信息：\n","\n","1. 列出可用的表；\n","2. 检索三个表的架构；\n","3. 通过连接操作查询多个表。\n","\n","然后，代理能够使用最终查询的结果来生成原始问题的答案。\n","\n","agent同样能够处理定性问题："]},{"cell_type":"code","execution_count":null,"id":"920aac47","metadata":{"colab":{"base_uri":"https://localhost:8080/"},"id":"920aac47","outputId":"ccb24152-048a-46dc-b49c-9cef32624f52","executionInfo":{"status":"ok","timestamp":1718533128247,"user_tz":-480,"elapsed":7026,"user":{"displayName":"李辉","userId":"12972001611808140221"}}},"outputs":[{"output_type":"stream","name":"stdout","text":["{'agent': {'messages': [AIMessage(content='', additional_kwargs={'tool_calls': [{'id': 'call_0jRwNptf1RHFPxOkUCtpNQuE', 'function': {'arguments': '{\"table_names\":\"playlisttrack\"}', 'name': 'sql_db_schema'}, 'type': 'function'}]}, response_metadata={'token_usage': {'completion_tokens': 17, 'prompt_tokens': 554, 'total_tokens': 571}, 'model_name': 'gpt-3.5-turbo', 'system_fingerprint': 'fp_811936bd4f', 'finish_reason': 'tool_calls', 'logprobs': None}, id='run-d2fc6d74-bf97-4063-856b-7938e35a52c0-0', tool_calls=[{'name': 'sql_db_schema', 'args': {'table_names': 'playlisttrack'}, 'id': 'call_0jRwNptf1RHFPxOkUCtpNQuE'}], usage_metadata={'input_tokens': 554, 'output_tokens': 17, 'total_tokens': 571})]}}\n","----\n","{'tools': {'messages': [ToolMessage(content=\"Error: table_names {'playlisttrack'} not found in database\", name='sql_db_schema', tool_call_id='call_0jRwNptf1RHFPxOkUCtpNQuE')]}}\n","----\n","{'agent': {'messages': [AIMessage(content='', additional_kwargs={'tool_calls': [{'id': 'call_GvCfLVteA8dMaBkzYg5qUjBJ', 'function': {'arguments': '{}', 'name': 'sql_db_list_tables'}, 'type': 'function'}]}, response_metadata={'token_usage': {'completion_tokens': 12, 'prompt_tokens': 592, 'total_tokens': 604}, 'model_name': 'gpt-3.5-turbo', 'system_fingerprint': 'fp_811936bd4f', 'finish_reason': 'tool_calls', 'logprobs': None}, id='run-e1e756fc-a5cb-47bd-96f4-799d33a24f5a-0', tool_calls=[{'name': 'sql_db_list_tables', 'args': {}, 'id': 'call_GvCfLVteA8dMaBkzYg5qUjBJ'}], usage_metadata={'input_tokens': 592, 'output_tokens': 12, 'total_tokens': 604})]}}\n","----\n","{'tools': {'messages': [ToolMessage(content='Album, Artist, Customer, Employee, Genre, Invoice, InvoiceLine, MediaType, Playlist, PlaylistTrack, Track', name='sql_db_list_tables', tool_call_id='call_GvCfLVteA8dMaBkzYg5qUjBJ')]}}\n","----\n","{'agent': {'messages': [AIMessage(content='The `playlisttrack` table contains information about the association between playlists and tracks. I will now query the schema of the `playlisttrack` table to provide more details.', additional_kwargs={'tool_calls': [{'id': 'call_evh9F3Hi4tM8tcZhIYsXskeo', 'function': {'arguments': '{\"table_names\":\"playlisttrack\"}', 'name': 'sql_db_schema'}, 'type': 'function'}]}, response_metadata={'token_usage': {'completion_tokens': 52, 'prompt_tokens': 637, 'total_tokens': 689}, 'model_name': 'gpt-3.5-turbo', 'system_fingerprint': 'fp_811936bd4f', 'finish_reason': 'tool_calls', 'logprobs': None}, id='run-b42d5135-f462-4ae7-8ea1-b6e5e3df9c1f-0', tool_calls=[{'name': 'sql_db_schema', 'args': {'table_names': 'playlisttrack'}, 'id': 'call_evh9F3Hi4tM8tcZhIYsXskeo'}], usage_metadata={'input_tokens': 637, 'output_tokens': 52, 'total_tokens': 689})]}}\n","----\n","{'tools': {'messages': [ToolMessage(content=\"Error: table_names {'playlisttrack'} not found in database\", name='sql_db_schema', tool_call_id='call_evh9F3Hi4tM8tcZhIYsXskeo')]}}\n","----\n","{'agent': {'messages': [AIMessage(content='I apologize, but it seems that there is an issue with retrieving the schema for the `playlisttrack` table. I will attempt to query the schema again to provide you with the necessary information.', additional_kwargs={'tool_calls': [{'id': 'call_X4P3IF7kslBCdNruMeoXyzbl', 'function': {'arguments': '{\"query\":\"PRAGMA table_info(playlisttrack)\"}', 'name': 'sql_db_query'}, 'type': 'function'}]}, response_metadata={'token_usage': {'completion_tokens': 62, 'prompt_tokens': 713, 'total_tokens': 775}, 'model_name': 'gpt-3.5-turbo', 'system_fingerprint': 'fp_811936bd4f', 'finish_reason': 'tool_calls', 'logprobs': None}, id='run-481c06c8-e90d-4995-9cc9-302bbce5b0e3-0', tool_calls=[{'name': 'sql_db_query', 'args': {'query': 'PRAGMA table_info(playlisttrack)'}, 'id': 'call_X4P3IF7kslBCdNruMeoXyzbl'}], usage_metadata={'input_tokens': 713, 'output_tokens': 62, 'total_tokens': 775})]}}\n","----\n","{'tools': {'messages': [ToolMessage(content=\"[(0, 'PlaylistId', 'INTEGER', 1, None, 1), (1, 'TrackId', 'INTEGER', 1, None, 2)]\", name='sql_db_query', tool_call_id='call_X4P3IF7kslBCdNruMeoXyzbl')]}}\n","----\n","{'agent': {'messages': [AIMessage(content='The `playlisttrack` table consists of two columns:\\n1. `PlaylistId` - an integer representing the ID of the playlist.\\n2. `TrackId` - an integer representing the ID of the track associated with the playlist.\\n\\nThis table serves to associate tracks with playlists based on their respective IDs. If you have any specific queries or tasks related to this table, feel free to let me know!', response_metadata={'token_usage': {'completion_tokens': 82, 'prompt_tokens': 823, 'total_tokens': 905}, 'model_name': 'gpt-3.5-turbo', 'system_fingerprint': 'fp_811936bd4f', 'finish_reason': 'stop', 'logprobs': None}, id='run-b5900087-b950-4459-b59e-c3c5eaad14f9-0', usage_metadata={'input_tokens': 823, 'output_tokens': 82, 'total_tokens': 905})]}}\n","----\n"]}],"source":["for s in agent_executor.stream(\n","{\"messages\": [HumanMessage(content=\"Describe the playlisttrack table\")]}\n","):\n","  print(s)\n","  print(\"----\")"]},{"cell_type":"markdown","id":"4ce9825a","metadata":{"id":"4ce9825a"},"source":["\n","\n","### 处理查询专有名词（高基数(high-cardinality)列）\n","\n","具有大量不同值的列。 例如，一个存储员工ID的列，每个员工都有唯一的ID，那么这个列就是高基数列。\n","\n","为了筛选包含专有名词的列，例如地址、歌曲名称或艺术家，我们首先需要仔细检查拼写，以便正确筛选数据。\n","\n","我们可以通过创建一个包含数据库中所有不同专有名词的向量存储来实现这一点。然后，每次用户在他们的问题中包含专有名词时，代理就可以查询这个向量存储，以找到那个词的正确拼写。通过这种方式，agent可以确保在构建目标查询之前理解用户所指的实体。\n","\n","首先，我们需要每个实体的唯一值，为此我们定义了一个函数，该函数将结果解析为元素列表："]},{"cell_type":"code","execution_count":null,"id":"299d84b6","metadata":{"id":"299d84b6","colab":{"base_uri":"https://localhost:8080/"},"executionInfo":{"status":"ok","timestamp":1718533423379,"user_tz":-480,"elapsed":7,"user":{"displayName":"李辉","userId":"12972001611808140221"}},"outputId":"c3eda44b-30dd-48da-e5ef-d3f65884a78a"},"outputs":[{"output_type":"execute_result","data":{"text/plain":["[\"Schubert: The Late String Quartets & String Quintet ( CD's)\",\n"," 'A TempestadeTempestade Ou O Livro Dos Dias',\n"," 'BBC Sessions [Disc ] [Live]',\n"," 'Appetite for Destruction',\n"," 'My Way: The Best Of Frank Sinatra [Disc ]']"]},"metadata":{},"execution_count":28}],"source":["import ast\n","import re\n","def query_as_list(db, query):\n","  res = db.run(query)\n","  res = [el for sub in ast.literal_eval(res) for el in sub if el]\n","  res = [re.sub(r\"\\b\\d+\\b\", \"\", string).strip() for string in res]\n","  return list(set(res))\n","artists = query_as_list(db, \"SELECT Name FROM Artist\")\n","albums = query_as_list(db, \"SELECT Title FROM Album\")\n","albums[:5]"]},{"cell_type":"markdown","id":"dc3e0db2","metadata":{"id":"dc3e0db2"},"source":["\n","使用这个函数，我们可以创建一个检索工具，代理可以根据自己的判断执行。"]},{"cell_type":"code","execution_count":null,"id":"d7036547","metadata":{"id":"d7036547"},"outputs":[],"source":["from langchain.agents.agent_toolkits import create_retriever_tool\n","from langchain_community.vectorstores import FAISS\n","from langchain_openai import OpenAIEmbeddings\n","vector_db = FAISS.from_texts(artists + albums, OpenAIEmbeddings())\n","retriever = vector_db.as_retriever(search_kwargs={\"k\": 5})\n","description = \"\"\"Use to look up values to filter on. Input is an approximate spelling of the proper noun, output is \\\n","valid proper nouns. Use the noun most similar to the search.\"\"\"\n","retriever_tool = create_retriever_tool(\n","    retriever,\n","    name=\"search_proper_nouns\",\n","    description=description,\n",")\n","tools = [retriever_tool] + tools"]},{"cell_type":"markdown","id":"0a5533b5","metadata":{"id":"0a5533b5"},"source":["**API 调用:**[create_retriever_tool](https://api.python.langchain.com/en/latest/tools/langchain_core.tools.create_retriever_tool.html) | [FAISS](https://api.python.langchain.com/en/latest/vectorstores/langchain_community.vectorstores.faiss.FAISS.html) | [OpenAIEmbeddings](https://api.python.langchain.com/en/latest/embeddings/langchain_openai.embeddings.base.OpenAIEmbeddings.html)"]},{"cell_type":"code","execution_count":null,"id":"d8c5cd28","metadata":{"id":"d8c5cd28","colab":{"base_uri":"https://localhost:8080/"},"executionInfo":{"status":"ok","timestamp":1718534028694,"user_tz":-480,"elapsed":1110,"user":{"displayName":"李辉","userId":"12972001611808140221"}},"outputId":"c6db520a-3a08-45c6-8b46-9ac69904bd41"},"outputs":[{"output_type":"stream","name":"stdout","text":["Alice In Chains\n","\n","Alanis Morissette\n","\n","Pearl Jam\n","\n","Pearl Jam\n","\n","Audioslave\n"]}],"source":["print(retriever_tool.invoke(\"Alice Chains\"))"]},{"cell_type":"markdown","id":"1204f9d0","metadata":{"id":"1204f9d0"},"source":["这样，如果agent确定需要根据像“Alice Chains”这样的艺术家来编写过滤器，它首先可以使用检索工具来观察某一列的相关值。"]},{"cell_type":"code","execution_count":null,"id":"5b0e32bd","metadata":{"id":"5b0e32bd"},"outputs":[],"source":["system = \"\"\"You are an agent designed to interact with a SQL database.\n","Given an input question, create a syntactically correct SQLite query to run, then look at the results of the query and return the answer.\n","Unless the user specifies a specific number of examples they wish to obtain, always limit your query to at most 5 results.\n","You can order the results by a relevant column to return the most interesting examples in the database.\n","Never query for all the columns from a specific table, only ask for the relevant columns given the question.\n","You have access to tools for interacting with the database.\n","Only use the given tools. Only use the information returned by the tools to construct your final answer.\n","You MUST double check your query before executing it. If you get an error while executing a query, rewrite the query and try again.\n","DO NOT make any DML statements (INSERT, UPDATE, DELETE, DROP etc.) to the database.\n","You have access to the following tables: {table_names}\n","If you need to filter on a proper noun, you must ALWAYS first look up the filter value using the \"search_proper_nouns\" tool!\n","Do not try to guess at the proper name - use this function to find similar ones.\"\"\".format(\n","    table_names=db.get_usable_table_names()\n",")\n","system_message = SystemMessage(content=system)\n","agent = create_react_agent(llm, tools, messages_modifier=system_message)"]},{"cell_type":"code","execution_count":null,"id":"b6235ac2","metadata":{"id":"b6235ac2","colab":{"base_uri":"https://localhost:8080/"},"executionInfo":{"status":"ok","timestamp":1718534172144,"user_tz":-480,"elapsed":5497,"user":{"displayName":"李辉","userId":"12972001611808140221"}},"outputId":"1aa57963-8fd8-4a39-fe40-fa3056d6f2b2"},"outputs":[{"output_type":"stream","name":"stdout","text":["{'agent': {'messages': [AIMessage(content='', additional_kwargs={'tool_calls': [{'id': 'call_mnBaK1SGfXJlirOBNgn6QgmR', 'function': {'arguments': '{\"query\":\"ant\\\\\\\\u00f4nio carlo jobim\"}', 'name': 'search_proper_nouns'}, 'type': 'function'}]}, response_metadata={'token_usage': {'completion_tokens': 27, 'prompt_tokens': 739, 'total_tokens': 766}, 'model_name': 'gpt-3.5-turbo', 'system_fingerprint': 'fp_811936bd4f', 'finish_reason': 'tool_calls', 'logprobs': None}, id='run-f68b292c-de56-4925-9dde-5b6c125297c2-0', tool_calls=[{'name': 'search_proper_nouns', 'args': {'query': 'ant\\\\u00f4nio carlo jobim'}, 'id': 'call_mnBaK1SGfXJlirOBNgn6QgmR'}], usage_metadata={'input_tokens': 739, 'output_tokens': 27, 'total_tokens': 766})]}}\n","----\n","{'tools': {'messages': [ToolMessage(content='Antônio Carlos Jobim\\n\\nCaetano Veloso\\n\\nJoão Gilberto\\n\\nChico Buarque\\n\\nGonzaguinha', name='search_proper_nouns', tool_call_id='call_mnBaK1SGfXJlirOBNgn6QgmR')]}}\n","----\n","{'agent': {'messages': [AIMessage(content='', additional_kwargs={'tool_calls': [{'id': 'call_tRhAT34lxIGV3hMJ0sGyU7Tf', 'function': {'arguments': '{\"query\":\"SELECT COUNT(*) AS album_count FROM Album WHERE ArtistId IN (SELECT ArtistId FROM Artist WHERE Name = \\'Antônio Carlos Jobim\\')\"}', 'name': 'sql_db_query'}, 'type': 'function'}]}, response_metadata={'token_usage': {'completion_tokens': 43, 'prompt_tokens': 805, 'total_tokens': 848}, 'model_name': 'gpt-3.5-turbo', 'system_fingerprint': 'fp_811936bd4f', 'finish_reason': 'tool_calls', 'logprobs': None}, id='run-7d516ede-203a-47c1-98ee-203febae6422-0', tool_calls=[{'name': 'sql_db_query', 'args': {'query': \"SELECT COUNT(*) AS album_count FROM Album WHERE ArtistId IN (SELECT ArtistId FROM Artist WHERE Name = 'Antônio Carlos Jobim')\"}, 'id': 'call_tRhAT34lxIGV3hMJ0sGyU7Tf'}], usage_metadata={'input_tokens': 805, 'output_tokens': 43, 'total_tokens': 848})]}}\n","----\n","{'tools': {'messages': [ToolMessage(content='[(2,)]', name='sql_db_query', tool_call_id='call_tRhAT34lxIGV3hMJ0sGyU7Tf')]}}\n","----\n","{'agent': {'messages': [AIMessage(content='Antônio Carlos Jobim has 2 albums.', response_metadata={'token_usage': {'completion_tokens': 12, 'prompt_tokens': 861, 'total_tokens': 873}, 'model_name': 'gpt-3.5-turbo', 'system_fingerprint': 'fp_811936bd4f', 'finish_reason': 'stop', 'logprobs': None}, id='run-77a9d649-fbb0-49e4-9871-24bf91574286-0', usage_metadata={'input_tokens': 861, 'output_tokens': 12, 'total_tokens': 873})]}}\n","----\n"]}],"source":["for s in agent.stream(\n","{\"messages\": [HumanMessage(content=\"How many albums does antônio carlo jobim have?\")]}\n","):\n","  print(s)\n","  print(\"----\")"]},{"cell_type":"code","source":["print(tools)"],"metadata":{"colab":{"base_uri":"https://localhost:8080/"},"id":"Y3uxVLHJTGRp","executionInfo":{"status":"ok","timestamp":1718327530540,"user_tz":-480,"elapsed":6,"user":{"displayName":"李辉","userId":"12972001611808140221"}},"outputId":"0c82397a-d955-4118-e3e6-2c116c0f2bc5"},"id":"Y3uxVLHJTGRp","execution_count":null,"outputs":[{"output_type":"stream","name":"stdout","text":["[QuerySQLDataBaseTool(description=\"Input to this tool is a detailed and correct SQL query, output is a result from the database. If the query is not correct, an error message will be returned. If an error is returned, rewrite the query, check the query, and try again. If you encounter an issue with Unknown column 'xxxx' in 'field list', use sql_db_schema to query the correct table fields.\", db=<langchain_community.utilities.sql_database.SQLDatabase object at 0x7d2118a61de0>), InfoSQLDatabaseTool(description='Input to this tool is a comma-separated list of tables, output is the schema and sample rows for those tables. Be sure that the tables actually exist by calling sql_db_list_tables first! Example Input: table1, table2, table3', db=<langchain_community.utilities.sql_database.SQLDatabase object at 0x7d2118a61de0>), ListSQLDatabaseTool(db=<langchain_community.utilities.sql_database.SQLDatabase object at 0x7d2118a61de0>), QuerySQLCheckerTool(description='Use this tool to double check if your query is correct before executing it. Always use this tool before executing a query with sql_db_query!', db=<langchain_community.utilities.sql_database.SQLDatabase object at 0x7d2118a61de0>, llm=ChatOpenAI(client=<openai.resources.chat.completions.Completions object at 0x7d20fdc886a0>, async_client=<openai.resources.chat.completions.AsyncCompletions object at 0x7d20fdc660b0>, openai_api_key=SecretStr('**********'), openai_api_base='https://api.openai-hk.com/v1', openai_proxy=''), llm_chain=LLMChain(prompt=PromptTemplate(input_variables=['dialect', 'query'], template='\\n{query}\\nDouble check the {dialect} query above for common mistakes, including:\\n- Using NOT IN with NULL values\\n- Using UNION when UNION ALL should have been used\\n- Using BETWEEN for exclusive ranges\\n- Data type mismatch in predicates\\n- Properly quoting identifiers\\n- Using the correct number of arguments for functions\\n- Casting to the correct data type\\n- Using the proper columns for joins\\n\\nIf there are any of the above mistakes, rewrite the query. If there are no mistakes, just reproduce the original query.\\n\\nOutput the final SQL query only.\\n\\nSQL Query: '), llm=ChatOpenAI(client=<openai.resources.chat.completions.Completions object at 0x7d20fdc886a0>, async_client=<openai.resources.chat.completions.AsyncCompletions object at 0x7d20fdc660b0>, openai_api_key=SecretStr('**********'), openai_api_base='https://api.openai-hk.com/v1', openai_proxy='')))]\n"]}]},{"cell_type":"markdown","id":"8c26a336","metadata":{"id":"8c26a336"},"source":["\n","正如我们所见，agent 使用了 `search_proper_nouns` 工具，以便正确查询数据库中这位特定艺术家的信息。"]}],"metadata":{"colab":{"provenance":[]},"jupytext":{"cell_metadata_filter":"-all","main_language":"python","notebook_metadata_filter":"-all"},"kernelspec":{"display_name":"Python 3","name":"python3"},"language_info":{"name":"python"}},"nbformat":4,"nbformat_minor":5}